
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-- 系统验证表
-- 用户，权限，结构...
----------------------------------------------------------------------- 
----------------------------------------------------------------------- 
-- 用户表
-- 注：
-- 只有状态为0,1的用户为有效用户,当用户状态为1时,必须填写endtime字段,endtime为空或大于当前时间则不能访问系统。
create table h_auth_users(
	id 			  int			not null,					/* 编号 */
	username	  varchar2(20)	not null,					/* 用户名(账号) */
	password  	  varchar2(40) 	not null,                   /* 密码 */
	realname      varchar2(50) 	not null,                   /* 真实姓名 */
  	gender        int			default 0 not null,         /* 性别 0.男,1.女 */
  	phone		  varchar2(100)	default '-' not null,       /* 电话 */
  	email         varchar2(100)	default '-' not null,       /* 邮件 */
  	createtime	  date			default sysdate not null,	/* 创建时间 */
  	endtime		  date			null,						/* 试用结束时间 */
	state		  int			default 0 not null,			/* 状态0.正常,1.试用 2.注销 */
	userid		  int 			default 2 not null,			/* 默认为dev创建 */
	primary key(id)
);
create unique index h_auth_user_u on h_auth_users(username);
create index h_auth_user_id_state on h_auth_users(id,state);
-- 用户序列
create sequence h_auth_user_seq start with 1 increment by 1;
-- h_auth_users.id自动赋值
create or replace trigger h_auth_users_id_auto
  before insert on h_auth_users
  for each row
begin
  select h_auth_user_seq.nextval into :new.id from dual;
end;
/
----------------------------------------------------------------------- 
-- 角色表
create table h_auth_roles(
	id			int				not null,					/* 编号 */
	name   		varchar2(50) 	not null,					/* 角色名 */
	note	   	varchar2(200)	null,						/* 说明 */
	userid		int				not null,					/* 创建该角色的用户,h_auth_users.id,是由哪个用户创建的该角色*/
	roleid		int				not null,					/* 创建该角色的角色,h_auth_roles.id,是由哪个角色创建的该角色*/
	primary key(id)
);
-- 角色序列
create sequence h_auth_role_seq start with 1 increment by 1;
-- h_auth_users.id自动赋值
create or replace trigger h_auth_roles_auto
  before insert on h_auth_roles
  for each row
begin
  select h_auth_role_seq.nextval into :new.id from dual;
end;
/
-- 包含username,rolename的视图 
create view v_auth_roles as 
select r.*,u.username username,r1.name rolename from h_auth_roles r 
   left join h_auth_users u on u.id=r.userid
   left join h_auth_roles r1 on r1.id=r.roleid;
----------------------------------------------------------------------- 
-- 用户与角色对应表,用户与角色是多对多的关系
create table h_auth_role_users(
	id			int				not null,					/* 编号 */
	roleid   	int 			not null,					/* 角色id,h_auth_roles.id */
	userid		int				not null,					/* 用户id,h_auth_users.id */
	primary key(id)
);
--级联操作
--向h_auth_role_users表插入记录时会检查对应的h_auth_roles.id,h_auth_users.id是否存在,不存在则添加失败。
--删除表h_auth_roles,h_auth_users中记录时会级联删除h_auth_role_users中对应的记录。
alter table h_auth_role_users modify roleid REFERENCES h_auth_roles(id) ON DELETE CASCADE;
alter table h_auth_role_users modify userid REFERENCES h_auth_users(id) ON DELETE CASCADE;
--序列
create sequence h_auth_role_user_seq start with 1 increment by 1;
-- h_auth_role_users.id自动赋值
create or replace trigger h_auth_role_users_auto
  before insert on h_auth_role_users
  for each row
begin
  select h_auth_role_user_seq.nextval into :new.id from dual;
end;
/
--同时取userid,roleid对应的username,rolename
create view v_auth_role_users as
select ru.*,u.username username,r.name rolename,r.note note from h_auth_role_users ru
   left join h_auth_users u on u.id=ru.userid
   left join h_auth_roles r on r.id=ru.roleid;
----------------------------------------------------------------------- 

-- 权限树结构
create table h_auth_trees(
	id			number(10) 			not null,				/* 编号 */
	pid			number(10) 			not null,				/* 父权限树id */
	havecid		number(1) 			default 0 not null,		/* 是否有子栏目，1为有，0为无 */
	oid			number(10) 			not null,				/* 所有栏目的排列循序 */
	alevel		number(10) 			not null,				/* 栏目级别 */
	title		varchar2(50) 		not null,				/* 标题 */
	kind		number(1)			default 0 not null,		/* 节点类型，0为静态权限节点 1为动态权限节点 */ 
	note		varchar2(100) 		null,					/* 备注 */
	state		number(1) 			default 0 not null,		/* 状态 0.正常,1.删除,2.隐藏*/
	primary key(id)
);
create index h_a__t_oid on h_auth_trees(oid);
create index h_a__t_state_pid on h_auth_trees(state,pid);
-- 树序列
create sequence h_auth_tree_seq start with 101 increment by 1;


-- 存储角色权限分配情况表
-- 注：当roleid=-1时表示所有的角色都拥有该authid的authkind权限
create table h_auth_role_infos(
	id 			number(10) 			not null,				/* 编号 */
	authid		number(10) 		not null,				/* 权限树编号,h_auth_trees.id */
	authkind	varchar2(10)		not null,				/* 信息维护权限,h_auth_kinds.kind */
	roleid		number(10)		not null,				/* 角色编号,h_auth_roles.id */
	primary key(id)
);
alter table h_auth_role_infos modify roleid REFERENCES h_auth_roles(id) ON DELETE CASCADE;

-- 角色权限序列
create sequence h_auth_role_info_seq start with 1 increment by 1;
create index h_auth_r_i_r_a on h_auth_role_infos(roleid,authid);

create view v_auth_role_infos as 
select r.*,u.name rolename, u.note rolenote, k.name kindname from h_auth_role_infos r left join h_auth_roles u on u.id=r.roleid  left join h_auth_kinds k on r.authkind=k.kind;

-- 存储用户权限分配情况表
-- 注：当userid=-1时表示所有的用户都拥有该authid的authkind权限
create table h_auth_user_infos(
	id 			number(10) 			not null,				/* 编号 */
	authid		number(10) 			not null,				/* 权限树编号,h_auth_trees.id */
	authkind	varchar2(10)		not null,				/* 信息维护权限,h_auth_kinds.kind */
	userid		number(10)			not null,				/* 用户编号,h_auth_users.id */
	primary key(id)
);
alter table h_auth_user_infos modify userid REFERENCES h_auth_users(id) ON DELETE CASCADE;
-- 用户权限序列
create sequence h_auth_user_info_seq start with 1 increment by 1;
create index h_auth_u_i_u_a on h_auth_user_infos(userid,authkind);


create view v_auth_user_infos as 
select r.*,u.username username,u.realname realname,k.name kindname from h_auth_user_infos r left join h_auth_users u on u.id=r.userid left join h_auth_kinds k on r.authkind=k.kind;

-- 动态权限分配,只有开发组成员才能操作该表
-- 注:非权限树分配的权限,例如由业务类别可能随时变动,而用户访问业务类别时是有限制的,
-- 这样权限树就不能满足需求。
-- authid在该表是唯一不可重复的，与h_auth_trees.id对应。
create table h_auth_dyna(
	id 			number(10) 			not null,				/* 编号 */
	tname		varchar2(50)		not null,				/* 表名,从哪个表读数据 */
	vcol		varchar2(50)		not null,				/* tname表中用来显示的字段名 */
	pcol		varchar2(50)		not null,				/* tname表中用来分配权限的字段名 */
	condition	varchar2(50)		null,					/* 检索条件 */
	authid		number(10) 			not null,				/* 权限树编号,h_auth_trees.id */
	primary key(id)
);
create sequence h_auth_dyna_seq start with 1 increment by 1;


-- 存储角色动态权限分配情况表
create table h_auth_dyna_role_infos(
	id 			number(10) 			not null,				/* 编号 */
	authid		number(10) 			not null,				/* 权限树编号,h_auth_trees.id */
-- 	vcolvalue	varchar2(100) 		not null,				/* 显示字段的字段值 */
	pcolvalue	varchar2(100)		not null,				/* 权限分配字段的字段值 */
	authkind	varchar2(10)		not null,				/* 信息维护权限,h_auth_kinds.kind */
	roleid		number(10)			not null,				/* 用户编号,h_auth_roles.id */
	primary key(id)
);
alter table h_auth_dyna_role_infos modify roleid REFERENCES h_auth_roles(id) ON DELETE CASCADE;
-- 用户权限序列
create sequence h_auths_dyna_role_info_seq start with 1 increment by 1;
create index h_auth_d_r_i_r_p on h_auth_dyna_role_infos(roleid,pcolvalue);

create view v_auth_dyna_role_infos as 
select r.*,u.name rolename, u.note rolenote, k.name kindname from h_auth_dyna_role_infos r left join h_auth_roles u on u.id=r.roleid  left join h_auth_kinds k on r.authkind=k.kind;


-- 存储用户动态权限分配情况表
-- 注:该表的使用方法,例:
-- 1.取出所有的业务列表,包括用来分配权限的字段值
-- 2.与h_auth_diny_user_infos中对应tname,pcolvalue的值比较,只取存在的值。
create table h_auth_dyna_user_infos(
	id 			number(10) 			not null,				/* 编号 */
	authid		number(10) 			not null,				/* 权限树编号,h_auth_trees.id */
--	vcolvalue	varchar2(100)		not null,				/* 显示字段的字段值 */
	pcolvalue	varchar2(100)		not Null,				/* 权限分配字段的字段值 */
	authkind	varchar2(10)		not null,				/* 信息维护权限,h_auth_kinds.kind */
	userid		number(10)			not null,				/* 用户编号,h_auth_users.id */
	primary key(id)
);
alter table h_auth_dyna_user_infos modify userid REFERENCES h_auth_users(id) ON DELETE CASCADE;
-- 用户权限序列
create sequence h_auth_dyna_user_info_seq start with 1 increment by 1;
create index h_auth_d_u_i_u_p on h_auth_dyna_user_infos(userid,pcolvalue);

create view v_auth_dyna_user_infos as 
select r.*,u.username username, u.realname realname, k.name kindname from h_auth_dyna_user_infos r left join h_auth_users u on u.id=r.userid  left join h_auth_kinds k on r.authkind=k.kind;


-- 权限树节点对应的action控制器
-- 注:该节点上的权限作用到对应的action控制器上
-- 只有用户的角色为开发人员时才能维护该表
create table h_auth_actions(
	id 			number(10) 			not null,				/* 编号 */
	authid		number(10) 			not null,				/* 权限树编号,h_auth_trees.id */
	actionclass	 varchar2(50)		not null,				/* 控制器的名字,如com.handy.permission.action.UserAction */
	authkindid	number(10) 			not null,				/* h_auth_kinds编号 */
	primary key(id)
);
create index h_auth_action_a on h_auth_actions(actionclass);
-- 序列
create sequence h_auth_action_seq start with 1 increment by 1;

create view v_auth_actions as 
select r.*,k.kind kind,k.name kindname,k.note kindnote  from h_auth_actions r 
left join h_auth_kinds k on r.authkindid=k.kind  ;

create table h_auth_scriptactions(
	id 			number(10) 			not null,				/* 编号 */
	authid		number(10) 			not null,				/* 权限树编号,h_auth_trees.id */
	scid		varchar2(50)		not null,				/* 脚本唯一编号 */
	authkindid	number(10) 			not null,				/* h_auth_kinds编号 */
	primary key(id)
);
create index h_auth_scriptactions_s on h_auth_actionscripts(scid);
-- 序列
create sequence h_auth_scriptactions_seq start with 1 increment by 1;

create view v_auth_scriptactions as 
select r.*,k.kind kind,k.name kindname,k.note kindnote  from h_auth_scriptactions r 
left join h_auth_kinds k on r.authkindid=k.kind  ;



-- 权限种类
create table h_auth_kinds(
	id 			number(10) 			not null,				/* 编号 */
	kind		varchar2(10)        not null,				/* 权限键值 */
	name    	varchar2(10)		not null,				/* 权限名称 */
	note		varchar2(50),								/* 权限备注 */
	primary key(id)
);
-- 权限种类序列
create sequence h_auth_kind_seq start with 1 increment by 1;

-- 权限种类
create table h_auth_id_kinds(
	id 			number(10) 			not null,				/* 编号 */
	authkindid 	number(10) 			not null,				/* h_auth_kinds编号 */
	authid 		number(10) 			not null,				/* h_auth_tree编号 */
	primary key(id)
);
-- 权限种类序列
create sequence h_auth_id_kind_seq start with 1 increment by 1;

create view v_auth_id_kinds as 
select r.*,k.kind kind,k.name kindname,k.note kindnote, t.title title from h_auth_id_kinds r 
left join h_auth_kinds k on r.authkindid=k.kind  left join h_auth_trees t on r.authid=t.id;


select title , sum(case KINDNAME when '添加' then 1 else 0 end)as adds,
sum(case KINDNAME when '删除' then 1 else 0 end)as del,
sum(case KINDNAME when '修改' then 1 else 0 end)as mod,
sum(case KINDNAME when '查看' then 1 else 0 end)as brw,
sum(case KINDNAME when '复制' then 1 else 0 end)as copy,
sum(case KINDNAME when '审核' then 1 else 0 end)as im
from v_auth_id_kinds group by title;

/* 系统登录，退出，权限维护日志 */
create table h_auth_logs(
	id 			number(10) 			not null,				   	/* 编号 */
	nickname	varchar2(20)		not null,					/* 用户账号 */
	recorddate 	date 			    default sysdate not null ,	/* 访问日期 */
	note		varchar2(100)		null,						/* 事件记录 */
	primary key (id)
);
create sequence h_auth_log_seq start with 1 increment by 1;


-- ==================================================================
-- 视图
-- ==================================================================
--该视图为所有已分配权限的authid列表
create view v_auth_all as 
  select 't0-'||r.id id, r.authid, h.title treetilte, r.roleid,-1 userid,r.authkind kind,k.name kindname,'-1' pcolvalue from h_auth_role_infos r  left join h_auth_kinds k on r.authkind=k.kind  left join h_auth_trees h on r.authid=h.id union
  select 't2-'||u.id id, u.authid, h.title treetilte,-1 roleid,u.userid,u.authkind kind,k.name kindname,'-1' pcolvalue from h_auth_user_infos u  left join h_auth_kinds k on u.authkind=k.kind left join h_auth_trees h on u.authid=h.id union
  select 't1-'||dr.id id, dr.authid,h.title treetilte,dr.roleid,-1 userid,dr.authkind kind,k.name kindname,dr.pcolvalue pcolvalue from h_auth_dyna_role_infos dr  left join h_auth_kinds k on dr.authkind=k.kind left join h_auth_trees h on dr.authid=h.id union
  select 't3-'||du.id id, du.authid,h.title treetilte,-1 roleid,du.userid,du.authkind kind, k.name kindname, du.pcolvalue pcolvalue from h_auth_dyna_user_infos du  left join h_auth_kinds k on du.authkind=k.kind left join h_auth_trees h on du.authid=h.id ;

--该视图为所有已分配权限的authid列表(树结构)
--废弃:使用递归语句来查询
--create view v_auth_trees as select t.* , vt.* from h_auth_trees t,v_auth_ids vt where t.id=vt.authid;

-- ---------------------------------------------------------------------
-- 初始记录
-- ---------------------------------------------------------------------
-- 角色
insert into h_auth_roles values (1,'超级用户组','超级用户组',1);
insert into h_auth_roles values (2,'开发组','开发组',1);


-- 用户
insert into h_auth_users(username,password,realname) values('admin','e10adc3949ba59abbe56e057f20f883e','超级用户');
insert into h_auth_users(username,password,realname) values('dev','e10adc3949ba59abbe56e057f20f883e','程序开发人员');
-- 用户对应角色
insert into h_auth_role_users(roleid,userid)values(1,1);
insert into h_auth_role_users(roleid,userid)values(2,2);

insert into h_auth_trees values(1,-1,1,0,1,'权限树',0,'权限根节点,该节点不能被删除',0);
insert into h_auth_trees values(2,1,1,1,1,'系统管理',0,'是否可以操作权限分配,该节点不能被删除',0);
insert into h_auth_trees values(3,2,0,2,2,'用户管理',0,'是否充许分配用户,该节点不能被删除',0);
insert into h_auth_trees values(4,2,0,4,2,'角色管理',0,'是否充许分配角色,该节点不能被删除',0);
insert into h_auth_trees values(5,2,0,5,2,'权限分配',0,'是否充许为角色分配权限,该节点不能被删除',0);
insert into h_auth_trees values(6,2,0,6,2,'权限类别',0,'是否充许分配用户,该节点不能被删除',0);

insert into h_auth_trees values(10,1,1,10,1,'办公桌面',0,'仅仅是一个分类节点描述',0);
insert into h_auth_trees values(11,10,0,11,2,'日程安排',0,'为分配登录用户可查看哪些用户的日程安排',0);

-- 权限种类
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '1','添加','添加');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '2','删除','删除');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '3','修改','修改');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '4','查看','查看');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '6','复制','复制');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '7','审核','审核');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '8','自动审核','自动审核');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '9','权限分配','权限分配');


